package org.lzy.pt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.jivesoftware.database.DbConnectionManager;
import org.lzy.pt.bean.Server;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ServerListManager {

	private Logger Log = LoggerFactory.getLogger(ServerListManager.class);
	
	private static final String LOAD_SERVER = "SELECT T_DomainName,T_Port,T_Pwd FROM oftserver where T_DomainName=? and T_Port=?";
	
	private static final String SERVER_COUNT = "SELECT count(*) FROM oftserver";
	
	private static final String LOAD_SERVERLIST_LIMIT = "SELECT T_DomainName,T_Port,T_Pwd FROM oftserver LIMIT ?,?";

	private static final String ADD_SERVER = "insert into oftserver(T_DomainName,T_Port,T_PublicKey,T_Pwd) " +
			" values(?,?,?,?)";
	
	private static final String UPDATE_SERVER = "update oftserver set T_Pwd=? where T_DomainName=? and T_Port=?";
//	private static final String UPDATE_SERVER = "update oftserver set T_PublicKey=?, T_Pwd=? where T_DomainName=? and T_Port=?";
	
	private static final String DELETE_SERVER = "delete from oftserver where T_DomainName=? and T_Port=?";

	private static ServerListManager listManager = new ServerListManager();

	private ServerListManager() {
	}

	public static ServerListManager getInstance() {
		return listManager;
	}

	public List<Server> getServerList(int startIndex, int numResults) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	List<Server> result = runner.query(connection,LOAD_SERVERLIST_LIMIT, new BeanListHandler<Server>(Server.class),
    			new Object[]{startIndex,numResults});
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
	
	public Server getServer(String T_DomainName,int T_Port) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	Server result = runner.query(connection,LOAD_SERVER, new BeanHandler<Server>(Server.class),
    			new Object[]{T_DomainName,T_Port});
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
	
	public long getCount() throws SQLException {
		Connection connection = null;
		long result = 0 ;
		try {
			QueryRunner runner = new QueryRunner();
			connection = DbConnectionManager.getConnection();
			result = (Long) runner.query(connection, SERVER_COUNT,new ScalarHandler());
		} catch (SQLException e) {
			throw e;
		} finally {
			DbConnectionManager.closeConnection(connection);
		}
		return result;
	}
	
	private int setParam(PreparedStatement pstmt,String value, int cursor) throws SQLException{
		return setParam(pstmt, value, cursor, true);
	}
	
	private int setParam(PreparedStatement pstmt,int value, int cursor) throws SQLException{
		return setParam(pstmt, value, cursor, true);
	}
	
	private int setParam(PreparedStatement pstmt,String value, int cursor,boolean required) throws SQLException{
		if(value != null){
			cursor ++ ;
			pstmt.setString(cursor, value);
		}
		return cursor;
	}
	
	private int setParam(PreparedStatement pstmt,int value, int cursor,boolean required) throws SQLException{
		cursor ++ ;
		pstmt.setInt(cursor, value);
		return cursor;
	}
	
	public void addServer(String T_DomainName,int T_Port,byte[] T_PublicKey,String T_Pwd) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(ADD_SERVER);
			int i = 0;
			i = setParam(pstmt, T_DomainName, i);
			i = setParam(pstmt, T_Port, i);
			String temp = "";
			for (int j = 0; j < T_PublicKey.length; j++) {
				T_PublicKey[j] =  Short.decode( String.format("%02", T_PublicKey[j]) ).byteValue();
			}
			i = setParam(pstmt, temp, i);
			i = setParam(pstmt, T_Pwd, i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw e;
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
	}
	
	public void deleteServer(String T_DomainName,int T_Port) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(DELETE_SERVER);
			int i = 0;
			i = setParam(pstmt, T_DomainName, i);
			i = setParam(pstmt, T_Port, i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw e;
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
		
	}
	
	public void updateServer(Server server) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(UPDATE_SERVER);
			int i = 0;
//			i = setParam(pstmt, server.getT_PublicKey(), i);
			i = setParam(pstmt, server.getT_Pwd(), i);
			i = setParam(pstmt, server.getT_DomainName(), i);
			i = setParam(pstmt, server.getT_Port(), i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw e;
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
		
	}
	
	
}
